﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.OleDb;
using System.Data;

namespace syc
{
    public class ExcelConnection
    {
        public string ConnString;
        public OleDbConnection OldDbConn = new OleDbConnection();

        public ExcelConnection(string ExcelFileName)
        {
            ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFileName + ";Extended Properties=Excel 8.0";
            OldDbConn.ConnectionString = ConnString;
        }

        public void Open()
        {
            OldDbConn.Open();
        }

        public void Close()
        {
            OldDbConn.Close();
        }

        public DataSet ReadDS(string Range, string SheetName, string Condition)
        {
            string ExcelSelectStr = "SELECT " + Range + " FROM " + SheetName + " " + Condition; //查询语句
            OleDbDataAdapter ExcelSelectDA = new OleDbDataAdapter(ExcelSelectStr, OldDbConn); //执行查询
            DataSet ExcelDS = new DataSet();
            ExcelSelectDA.Fill(ExcelDS); //填充数据
            ExcelSelectDA.Dispose();
            return ExcelDS;
        }

        public OleDbDataReader ReadDR(string Range, string SheetName, string Condition)
        {
            string ExcelSelectStr = "SELECT " + Range + " FROM " + SheetName + " " + Condition;
            OleDbCommand ExcelSelectCmd = new OleDbCommand(ExcelSelectStr, OldDbConn);
            OleDbDataReader ExcelDR = ExcelSelectCmd.ExecuteReader();
            return ExcelDR;
        }

        public void Insert(string SheetName, string Range, string Value, string Condition)
        {
            string ExcelInsertStr = "INSERT INTO " + SheetName + " ( " + Range + " ) VALUES ( " + Value + " ) " + Condition;
            OleDbCommand ExcelInsertCmd = new OleDbCommand(ExcelInsertStr, OldDbConn);
            ExcelInsertCmd.ExecuteNonQuery();
            ExcelInsertCmd.Dispose();
        }

        public void Update(string SheetName, string UpdateValues, string Condition)
        {
            string ExcelUpdateStr = "UPDATE " + SheetName + " " + UpdateValues + " " + Condition;
            OleDbCommand ExcelUpdateCmd = new OleDbCommand(ExcelUpdateStr, OldDbConn);
            ExcelUpdateCmd.ExecuteNonQuery();
            ExcelUpdateCmd.Dispose();
        }

        public void Delete(string SheetName, string Condition)
        {
            string ExcelDeleteStr = "Delete " + SheetName + " " + Condition;
            OleDbCommand ExcelDeleteCmd = new OleDbCommand(ExcelDeleteStr, OldDbConn);
            ExcelDeleteCmd.ExecuteNonQuery();
            ExcelDeleteCmd.Dispose();
        }
    }


}
